package com.zbest.component.dal.sequence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 *
 *
 * @author czhou3306
 * @version $Id: DefaultSequenceDao.java, v 0.1 2014-11-30 下午5:24:53 czhou3306 Exp $
 */
public class DefaultSequenceDao implements SequenceDao, SequenceTableValidate {

    private static final Logger logger                          = LoggerFactory
            .getLogger(SequenceConstants.SEQUENCE_LOG_NAME);

    private static final int    DEFAULT_RETRY_TIMES             = 10;

    private static final String DEFAULT_TABLE_NAME              = "fin_sequence";
    private static final String DEFAULT_FIELD_NAME_SEQ_NAME     = "seq_name";
    private static final String DEFAULT_FIELD_NAME_SEQ_CURRENT  = "seq_current";
    private static final String DEFAULT_FIELD_NAME_MAX_VALUE    = "max_value";
    private static final String DEFAULT_FIELD_NAME_MIN_VALUE    = "min_value";
    private static final String DEFAULT_FIELD_NAME_STEP         = "step";
    private static final String DEFAULT_FIELD_NAME_GMT_MODIFIED = "gmt_modified";

    private String              tableName                       = DEFAULT_TABLE_NAME;
    private String              seqName                         = DEFAULT_FIELD_NAME_SEQ_NAME;
    private String              seqCurrentName                  = DEFAULT_FIELD_NAME_SEQ_CURRENT;
    private String              maxValueName                    = DEFAULT_FIELD_NAME_MAX_VALUE;
    private String              minValueName                    = DEFAULT_FIELD_NAME_MIN_VALUE;
    private String              stepName                        = DEFAULT_FIELD_NAME_STEP;
    private String              gmtModifiedName                 = DEFAULT_FIELD_NAME_GMT_MODIFIED;

    private DataSource          dataSource;

    /**
     * 重试次数
     */
    private int                 retryTimes                      = DEFAULT_RETRY_TIMES;


    public void validateTableColumn() throws SequenceException {
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        //查询表属性
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(buildSequenceTableColumnSql());
            stmt.setString(1, getTableName());
            stmt.setString(2, getStepName());
            rs = stmt.executeQuery();
            while (rs.next()) {
                String dataType = rs.getString("DATA_TYPE");
                String isNullable = rs.getString("IS_NULLABLE");
                if(!"decimal".equals(dataType) || !"NO".equals(isNullable))
                {
                    logger.error("column:[{}],dataType:[{}],isNullable:{[]}", getStepName(), dataType, isNullable);
                    throw new SequenceException(getTableName() + "表结构中" + getStepName() + "必须为decimal并且是notNullable");
                }
            }
        } catch (SQLException e) {
            logger.error("validate table column failed!", e);
            throw new SequenceException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception e) {
                logger.error("ERROR ## close resources has an error", e);
            }
        }
    }

    private String buildSequenceTableColumnSql() {
        StringBuilder buffer = new StringBuilder();
        buffer.append(" select DATA_TYPE,IS_NULLABLE from Information_schema.columns ");
        buffer.append("  where table_name = ? ");
        buffer.append("    and column_name = ?");
        return buffer.toString();
    }

    public SequenceRange nextRange(String name) throws SequenceException {
        if (name == null) {
            throw new IllegalArgumentException("序列名称不能为空");
        }

        long oldValue;
        long newValue;
        long stepValue;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        for (int i = 0; i < retryTimes + 1; ++i) {
            try {
                conn = dataSource.getConnection();
                stmt = conn.prepareStatement(buildSelectSequenceSql());
                stmt.setString(1, name);
                rs = stmt.executeQuery();
                rs.next();
                oldValue = rs.getLong(2);
                stepValue = rs.getLong(getStepName());

                if (oldValue < 0) {
                    StringBuilder message = new StringBuilder();
                    message.append("Sequence value cannot be less than zero, value = ").append(
                            oldValue);
                    message.append(", please check table ").append(getTableName());
                    throw new SequenceException(message.toString());
                }

                newValue = oldValue + stepValue;
                if (newValue > rs.getLong(getMaxValueName())) {
                    //如果循环了则再从min开始
                    newValue = rs.getLong(getMinValueName()) + stepValue;
                }

            } catch (SQLException e) {
                throw new SequenceException(e);
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                        rs = null;
                    }
                    if (stmt != null) {
                        stmt.close();
                        stmt = null;
                    }
                    if (conn != null) {
                        conn.close();
                        conn = null;
                    }
                } catch (Exception e) {
                    logger.error("ERROR ## close resources has an error", e);
                }
            }

            try {
                conn = dataSource.getConnection();
                stmt = conn.prepareStatement(buildUpdateSql());
                stmt.setLong(1, newValue);
                stmt.setString(2, name);
                stmt.setLong(3, oldValue);
                int affectedRows = stmt.executeUpdate();
                if (affectedRows == 0) {//乐观锁更新，直到更新成功.
                    // retry
                    continue;
                }
                return new SequenceRange(newValue - stepValue + 1, newValue);
            } catch (SQLException e) {
                throw new SequenceException(e);
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                        stmt = null;
                    }
                    if (conn != null) {
                        conn.close();
                        conn = null;
                    }
                } catch (Exception e) {
                    logger.error("ERROR ## close resources has an error", e);
                }
            }
        }

        throw new SequenceException("Retried too many times, retryTimes = " + retryTimes);
    }

    /**
     * 根据sequenceName 获取当前db里的指定sequence记录的各字段值
     *
     * selectSql: select value,min_value,max_value,step from sequence where name=?
     * nameColumn          sequence列名
     * minValueColumnName  最小值
     * maxValueColumnName  最大值
     * innerStepColumnName 内步长
     * @return
     * @throws SQLException
     * @throws SequenceException
     */
    public Map<String, Map<String, Object>> getSequenceRecordByName(String sequenceName)
            throws SQLException,
            SequenceException {
        Map<String, Map<String, Object>> records = new HashMap<String, Map<String, Object>>(0);
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        //查询一条记录,如果该记录不存在，就报异常
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(buildSelectSequenceSql());
            stmt.setString(1, sequenceName);
            rs = stmt.executeQuery();
            if (!rs.next()) {
                throw new SequenceException("can not find the record, name=" + sequenceName);
            }
            long min = rs.getLong(getMinValueName());
            long max = rs.getLong(getMaxValueName());
            long step = rs.getLong(getStepName());
            if (records.get(sequenceName) == null) {
                Map<String, Object> keyAndValue = new HashMap<String, Object>(0);
                keyAndValue.put(getMinValueName(), min);
                keyAndValue.put(getMaxValueName(), max);
                keyAndValue.put(getStepName(), step);
                records.put(sequenceName, keyAndValue);
            }
        } catch (SQLException e) {
            throw new SequenceException("ERROR ## get the sequence record failed,name="
                    + sequenceName, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception e) {
                logger.error("ERROR ## close resources has an error", e);
            }
        }
        return records;
    }

    private String buildSelectAllSequenceSql() {
        StringBuilder buffer = new StringBuilder();
        buffer.append("select ").append(getSeqName()).append(",");
        buffer.append(this.getSeqCurrentName()).append(",");
        buffer.append(this.getMinValueName()).append(",");
        buffer.append(this.getMaxValueName()).append(",");
        buffer.append(this.getStepName());
        buffer.append(" from ").append(this.getTableName());
        return buffer.toString();
    }

    /**
     *  获取当前db里所有的sequence记录的指定字段值
     *
     * selectSql  ：select name,min_value,max_value,step from table_name(default:sequence)
     * nameColumn  sequence列名
     * minValueColumnName 最小值
     * maxValueColumnName 最大值
     * innerStepColumnName 内步长
     * @return Map<String, Map<String, Object>> 外层key标识sequence名字，内层key表示最小、最大值以及步长等；
     * @throws SQLException
     */
    public Map<String, Map<String, Object>> getAllSequenceRecordName() throws SQLException {
        Map<String, Map<String, Object>> records = new HashMap<String, Map<String, Object>>(0);
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        //查询所有记录
        try {
            con = dataSource.getConnection();
            stmt = con.prepareStatement(buildSelectAllSequenceSql());
            rs = stmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString(getSeqName());
                long min = rs.getLong(getMinValueName());
                long max = rs.getLong(getMaxValueName());
                long step = rs.getLong(getStepName());
                if (records.get(name) == null) {
                    Map<String, Object> keyAndValue = new HashMap<String, Object>(0);
                    keyAndValue.put(getMinValueName(), min);
                    keyAndValue.put(getMaxValueName(), max);
                    keyAndValue.put(getStepName(), step);
                    records.put(name, keyAndValue);
                }
            }
        } catch (SQLException e) {
            logger.error("get all the sequence record failed!", e);
            throw e;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception e) {
                logger.error("ERROR ## close resources has an error", e);
            }
        }
        return records;
    }

    private String buildUpdateSql() {
        StringBuilder buffer = new StringBuilder();
        buffer.append("update ").append(getTableName());
        buffer.append(" set ").append(getSeqCurrentName()).append(" = ?, ");
        buffer.append(gmtModifiedName).append(" = now() where ");
        buffer.append(getSeqName()).append(" = ? and ");
        buffer.append(getSeqCurrentName()).append(" = ?");
        return buffer.toString();
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public int getRetryTimes() {
        return retryTimes;
    }

    public void setRetryTimes(int retryTimes) {
        if (retryTimes < 0) {
            throw new IllegalArgumentException(
                    "Property retryTimes cannot be less than zero, retryTimes = " + retryTimes);
        }

        this.retryTimes = retryTimes;
    }

    /**
     * 获取查询单个seq_name的sequence
     *
     * @return 格式：select value,min_value,max_value,step from sequence where name=?
     */
    public String buildSelectSequenceSql() {
        StringBuilder buffer = new StringBuilder();
        buffer.append("select ").append(getSeqName()).append(",");
        buffer.append(this.getSeqCurrentName()).append(",");
        buffer.append(this.getMinValueName()).append(",");
        buffer.append(this.getMaxValueName()).append(",");
        buffer.append(this.getStepName());
        buffer.append(" from ").append(this.getTableName());
        buffer.append(" where ").append(this.getSeqName()).append("= ?");
        return buffer.toString();
    }

    /**
     * Getter method for property <tt>tableName</tt>.
     *
     * @return property value of tableName
     */
    public String getTableName() {
        return tableName;
    }

    /**
     * Setter method for property <tt>tableName</tt>.
     *
     * @param tableName value to be assigned to property tableName
     */
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    /**
     * Getter method for property <tt>seqName</tt>.
     *
     * @return property value of seqName
     */
    public String getSeqName() {
        return seqName;
    }

    /**
     * Setter method for property <tt>seqName</tt>.
     *
     * @param seqName value to be assigned to property seqName
     */
    public void setSeqName(String seqName) {
        this.seqName = seqName;
    }

    /**
     * Getter method for property <tt>seqCurrentName</tt>.
     *
     * @return property value of seqCurrentName
     */
    public String getSeqCurrentName() {
        return seqCurrentName;
    }

    /**
     * Setter method for property <tt>seqCurrentName</tt>.
     *
     * @param seqCurrentName value to be assigned to property seqCurrentName
     */
    public void setSeqCurrentName(String seqCurrentName) {
        this.seqCurrentName = seqCurrentName;
    }

    /**
     * Getter method for property <tt>maxValueName</tt>.
     *
     * @return property value of maxValueName
     */
    public String getMaxValueName() {
        return maxValueName;
    }

    /**
     * Setter method for property <tt>maxValueName</tt>.
     *
     * @param maxValueName value to be assigned to property maxValueName
     */
    public void setMaxValueName(String maxValueName) {
        this.maxValueName = maxValueName;
    }

    /**
     * Getter method for property <tt>minValueName</tt>.
     *
     * @return property value of minValueName
     */
    public String getMinValueName() {
        return minValueName;
    }

    /**
     * Setter method for property <tt>minValueName</tt>.
     *
     * @param minValueName value to be assigned to property minValueName
     */
    public void setMinValueName(String minValueName) {
        this.minValueName = minValueName;
    }

    /**
     * Getter method for property <tt>stepName</tt>.
     *
     * @return property value of stepName
     */
    public String getStepName() {
        return stepName;
    }

    /**
     * Setter method for property <tt>stepName</tt>.
     *
     * @param stepName value to be assigned to property stepName
     */
    public void setStepName(String stepName) {
        this.stepName = stepName;
    }

}
